/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.insurance.cis.daos;

import com.insurance.cis.connection.CisConnection;
import com.insurance.cis.models.Employee;
import com.insurance.cis.scos.EmployeeSearchCondition;
import com.insurance.cis.utils.CisConstants;
import com.insurance.cis.utils.Utils;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author TruongLQ
 */
public class EmployeeDao {

    public Employee getEmployee(String username, String password) {
        try {
            final String SQL = "select * from Employee where Username=? and Password=?";
            PreparedStatement pst = CisConnection.getInstance().getSqlConnection().prepareStatement(SQL);
            pst.setString(1, username);
            pst.setString(2, password);
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                Employee employee = new Employee(
                        rs.getString(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4),
                        rs.getString(5),
                        rs.getDate(6),
                        rs.getInt(7));
                return employee;
            }
            return null;
        } catch (SQLException ex) {
            Logger.getLogger(EmployeeDao.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public Employee getEmployee(String employeeID) {
        try {
            final String SQL = "select * from Employee where EmployeeID=?";
            PreparedStatement pst = CisConnection.getInstance().getSqlConnection().prepareStatement(SQL);
            pst.setString(1, employeeID);
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                Employee employee = new Employee(
                        rs.getString(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4),
                        rs.getString(5),
                        rs.getDate(6),
                        rs.getInt(7));
                return employee;
            }
            return null;
        } catch (SQLException ex) {
            Logger.getLogger(EmployeeDao.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public boolean changePassword(String username, String newPassword) {
        try {
            final String SQL = "update Employee set Password=? where Username=?";
            PreparedStatement pst = CisConnection.getInstance().getSqlConnection().prepareStatement(SQL);
            pst.setString(1, newPassword);
            pst.setString(2, username);
            pst.executeUpdate();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(EmployeeDao.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    public List<Employee> searchEmployees(EmployeeSearchCondition sco) {
        final StringBuilder SQL = new StringBuilder("select * from Employee where 1=1 and GroupID <> 4");
        List<Employee> employees = new ArrayList<>();
        ResultSet rs;
        try {
            Map<Integer, Object> parameters = new HashMap<>();
            int count = 0;
            if (sco.isEmptyCondition()) {
                Statement st = CisConnection.getInstance().getSqlConnection().createStatement();
                rs = st.executeQuery(SQL.toString());
            } else {
                if (sco.getEmpID() != null
                        && !sco.getEmpID().isEmpty()) {
                    SQL.append(" and EmployeeID=? ");
                    parameters.put(++count, sco.getEmpID());
                }
                if (sco.getEmpName() != null
                        && !sco.getEmpName().isEmpty()) {
                    SQL.append(" and EmployeeName like ? ");
                    parameters.put(++count, "%" + sco.getEmpName() + "%");
                }
                if (sco.getUsername() != null
                        && !sco.getUsername().isEmpty()) {
                    SQL.append(" and Username like ? ");
                    parameters.put(++count, "%" + sco.getUsername() + "%");
                }
                if (sco.getGender() != null
                        && !sco.getGender().isEmpty()) {
                    SQL.append(" and Gender=? ");
                    parameters.put(++count, sco.getGender());
                }
                if (sco.getBirthday() != null) {
                    SQL.append(" and DOB=? ");
                    parameters.put(++count, Utils.convertDateToString(
                            sco.getBirthday(), CisConstants.DATE_YYYY_MM_DD_));
                }
                if (sco.getGroupID() != null) {
                    SQL.append(" and GroupID=? ");
                    parameters.put(++count, sco.getGroupID());
                }
                SQL.append(" order by EmployeeID asc");
                PreparedStatement pst = CisConnection.getInstance()
                        .getSqlConnection().prepareStatement(SQL.toString());
                for (Integer pos : parameters.keySet()) {
                    pst.setObject(pos, parameters.get(pos));
                }

                rs = pst.executeQuery();
            }
            buildEmployees(employees, rs);
            return employees;
        } catch (SQLException ex) {
            Logger.getLogger(EmployeeDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    private void buildEmployees(List<Employee> employees, ResultSet rs) throws SQLException {
        while (rs.next()) {
            Employee employee = new Employee(
                    rs.getString("EmployeeID"),
                    rs.getString("Username"),
                    rs.getString("Password"),
                    rs.getString("EmployeeName"),
                    rs.getString("Gender"),
                    rs.getDate("DOB"),
                    rs.getInt("GroupID"));
            employees.add(employee);
        }
    }

    public boolean checkIDExist(String employeeID) {
        final String SQL = "select * from Employee where EmployeeID=?";
        try {
            PreparedStatement pst = CisConnection.getInstance().getSqlConnection()
                    .prepareStatement(SQL);
            pst.setString(1, employeeID);
            ResultSet rs = pst.executeQuery();
            return rs.next();
        } catch (SQLException ex) {
            Logger.getLogger(EmployeeDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    public boolean checkUserNameExist(String username) {
        final String SQL = "select * from Employee where Username=?";
        try {
            PreparedStatement pst = CisConnection.getInstance().getSqlConnection()
                    .prepareStatement(SQL);
            pst.setString(1, username);
            ResultSet rs = pst.executeQuery();
            return rs.next();
        } catch (SQLException ex) {
            Logger.getLogger(EmployeeDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    public boolean deleteEmployee(String employeeID) {
        final String SQL = "delete from Employee where EmployeeID=?";
        try {
            PreparedStatement pst = CisConnection.getInstance().getSqlConnection()
                    .prepareStatement(SQL);
            pst.setString(1, employeeID);
            return pst.executeUpdate() > 0;
        } catch (SQLException ex) {
            Logger.getLogger(EmployeeDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    public boolean createEmployee(Employee employee) {
        final String SQL = "insert into Employee values(?,?,?,?,?,?,?)";
        try {
            PreparedStatement pst = CisConnection.getInstance().getSqlConnection()
                    .prepareStatement(SQL);
            pst.setString(1, employee.getCode());
            pst.setString(2, employee.getUserName());
            pst.setString(3, employee.getPassword());
            pst.setString(4, employee.getName());
            pst.setString(5, employee.getGender());
            if (employee.getDob() != null) {
                pst.setDate(6, new java.sql.Date(employee.getDob().getTime()));
            } else {
                pst.setDate(6, null);
            }
            pst.setInt(7, employee.getGroup().getId());
            return pst.executeUpdate() > 0;
        } catch (SQLException ex) {
            Logger.getLogger(EmployeeDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    public boolean updateEmployee(Employee employee) {
        final String SQL = "select * from Employee where EmployeeID=?";
        try {
            PreparedStatement pst = CisConnection.getInstance().getSqlConnection()
                    .prepareStatement(SQL, ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            pst.setString(1, employee.getCode());
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                rs.updateString(2, employee.getUserName());
                rs.updateString(3, employee.getPassword());
                rs.updateString(4, employee.getName());
                rs.updateString(5, employee.getGender());
                if (employee.getDob() != null) {
                    rs.updateDate(6, new java.sql.Date(employee.getDob().getTime()));
                } else {
                    rs.updateDate(6, null);
                }
                rs.updateInt(7, employee.getGroup().getId());
                rs.updateRow();
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(EmployeeDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }
}
